import xlrd

# 读取数据文件
data = xlrd.open_workbook('../../代码练习/baidu.xls')
table = data.sheets()[0]  # 假设数据在第一个sheet中

# 统计表格中有多少人
nrows = table.nrows
print("总共有 {} 人".format(nrows - 1))  # 减去表头所占的一行

# 统计办电信，联通，移动的用户数量并计算出三种用户的占比
# 定义中国移动、中国联通、中国电信的号码前缀列表
china_mobile_prefixes = ["134", "135", "136", "137", "138", "139", "147", "150", "152", "157", "158", "159", "178",
                         "182", "183", "184", "187", "188", "1703", "1705", "1706"]
china_unicom_prefixes = ["130", "131", "132", "145", "155", "156", "175", "176", "185", "186", "1704", "1707", "1708",
                         "1709", "171"]
china_telecom_prefixes = ["133", "149", "153", "173", "177", "180", "181", "189", "1700", "1701", "1702"]

# 初始化计数变量
china_mobile_count = 0
china_unicom_count = 0
china_telecom_count = 0

# 遍历数据的每一行
for i in range(1, nrows):  # 假设 nrows 是预先定义好的数据行数
    phone_number = table.cell_value(i, 5)  # 假设电话号码在第二列
    prefix = phone_number[:3]  # 提取电话号码的前三位作为前缀

    # 根据号码前缀判断属于哪个运营商，并进行计数
    if prefix == '170':
        prefix = phone_number[:4]
        if prefix in china_mobile_prefixes:
            china_mobile_count += 1
        elif prefix in china_unicom_prefixes:
            china_unicom_count += 1
        elif prefix in china_telecom_prefixes:
            china_telecom_count += 1
    elif prefix in china_mobile_prefixes:
        china_mobile_count += 1
    elif prefix in china_unicom_prefixes:
        china_unicom_count += 1
    elif prefix in china_telecom_prefixes:
        china_telecom_count += 1

# 计算总用户数量
total_operator_count = china_mobile_count + china_unicom_count + china_telecom_count

# 计算各运营商用户占比，避免除以零错误
if total_operator_count > 0:
    china_mobile_percent = china_mobile_count / total_operator_count * 100
    china_unicom_percent = china_unicom_count / total_operator_count * 100
    china_telecom_percent = china_telecom_count / total_operator_count * 100
else:
    china_mobile_percent = 0
    china_unicom_percent = 0
    china_telecom_percent = 0

# 输出结果
print("中国移动用户数量：{}，占比：{:.2f}%".format(china_mobile_count, china_mobile_percent))
print("中国联通用户数量：{}，占比：{:.2f}%".format(china_unicom_count, china_unicom_percent))
print("中国电信用户数量：{}，占比：{:.2f}%".format(china_telecom_count, china_telecom_percent))

# 总公司男女人数
male_count = 0
female_count = 0
for i in range(1, nrows):  # 遍历每一行数据
    gender = table.cell_value(i, 8)
    if gender == '男':
        male_count += 1
    elif gender == '女':
        female_count += 1

print("总公司男性人数：{}".format(male_count))
print("总公司女性人数：{}".format(female_count))

# 年龄超过45岁的老员工人数
senior_employee_count = 0
for i in range(1, nrows):  # 遍历每一行数据
    age = int(table.cell_value(i, 7))
    if age > 45:
        senior_employee_count += 1

print("年龄超过45岁的老员工人数：{}".format(senior_employee_count))

# 薪资高于8000元的高薪人员数量和薪资低于3000的底薪人员数量
high_salary_count = 0
low_salary_count = 0
for i in range(1, nrows):  # 遍历每一行数据
    salary = float(table.cell_value(i, 11))
    if salary > 8000:
        high_salary_count += 1
    elif salary < 3000:
        low_salary_count += 1

print("薪资高于8000元的高薪人员数量：{}".format(high_salary_count))
print("薪资低于3000的底薪人员数量：{}".format(low_salary_count))

# 统计去传媒公司的工作的人员数量
media_company_count = 0
for i in range(1, nrows):  # 遍历每一行数据
    company = table.cell_value(i, 13)
    if '传媒' in company:
        media_company_count += 1

print("去传媒公司的工作的人员数量：{}".format(media_company_count))

# 统计一下可能在疫情高危地区的人数
high_risk_area_count = 0
high_risk_areas = ['黑龙江', '北京', '福建', '四川']
for i in range(1, nrows):  # 遍历每一行数据
    area = table.cell_value(i, 9)
    for j in high_risk_areas:
        if j in area:
            high_risk_area_count += 1

print("可能在疫情高危地区的人数：{}".format(high_risk_area_count))
